-- @owner: wang-tianjing1
-- @date: 2024/8/19
-- @testpoint:before-update在表上支持语句级触发器，使用show triggers查询

@conn PrimaryDbBmode;
--step1:创建表 except:成功
drop table if exists test_trigger_src_tb0013;
create table test_trigger_src_tb0013(id1 int, id2 int, id3 int);
insert into test_trigger_src_tb0013 values(100,200,300);
drop table if exists test_trigger_des_tb0013;
create table test_trigger_des_tb0013(id1 int, id2 int, id3 int);
insert into test_trigger_des_tb0013 values(1,2,3);

--step2:创建触发器函数 except:成功
create or replace function tri_update_func0013() returns trigger as
$$
declare
begin  update test_trigger_des_tb0013 set id3 = 5 where id1=1;
return new;
end
$$ language plpgsql;
/

--step3:创建触发器 except:成功
create trigger update_trigger0013 before update on test_trigger_src_tb0013
for each statement execute procedure tri_update_func0013();
/

--step4:执行insert触发事件 except:成功
update test_trigger_src_tb0013 set id3=400 where id1=100;
select * from test_trigger_src_tb0013;

--step5:检查触发结果 except:成功
select * from test_trigger_des_tb0013;

--step6:查询触发器，添加where条件 except:成功
show triggers where "Trigger"='update_trigger0013';
show triggers where "Trigger"='update_trigger0013' and "Event"='UPDATE';

--step7:查询触发器，添加like条件 except:成功
show triggers where "Trigger" like 'update_trigger0013';

--step8:清理环境 except:成功
drop trigger if exists update_trigger0013 on test_trigger_src_tb0013;
drop function tri_update_func0013() cascade;
drop table test_trigger_src_tb0013;
drop table test_trigger_des_tb0013;